const express = require("express");
const mysql = require("mysql");
const router = express.Router();
const db = mysql.createPool({
    host:"101.200.212.23",
    user:"wish",
    password:"k8nKJDrxc2eEJsXB",
    database:"wish"
})
router.get("/banner",(req,res)=>{
    var sql = `SELECT * FROM banner2`
    db.query(sql,(err,result)=>{
        res.send({
            code:200,
            msg:"查询数据成功",
            val:result
        })
    })
})
router.post("/login",(req,res)=>{
    if(!req.body.user)return res.s("需要传递参数user用户名",[])
    if(!req.body.pwd)return res.s("需要传递参数pwd密码",[])
    var sql = `SELECT id,userName,email,tel FROM user WHERE userName=? OR tel=? OR email=?`;
    db.query(sql,[req.body.user,req.body.user,req.body.user],(err,result)=>{
       if(err) res.cc(err)
       console.log(result)
       if(result.length > 0 ){
           sql += `AND pwd=? `;
           db.query(sql,[req.body.user,req.body.user,req.body.user,req.body.pwd],(err,result)=>{
               console.log(result)
               if(err) res.cc(err);
               if(result.length > 0){
                   //取到用户名和密码对应的数据了
                   res.s("用户登录成功",result,200);
                }else{
                  res.s("密码错误",[]);
               }
           })
       }else{
          res.s("用户名未注册",[]);
       }
    })
})
router.get("/get_goods",(req,res)=>{
    // 如果什么参数都不传递过来 则取所有数据
    // 如果传入limit值 则取指定数据
    let sql = `SELECT id,name,img_cover,money_now,xl FROM goods_all`;
    let num = req.query.page*req.query.pageSize;
    if(req.query.page && req.query.pageSize){
        sql += ` LIMIT ${num},${req.query.pageSize}`;
    }else if(req.query.page && !req.query.pageSize){
        sql += ` LIMIT ${num},15`;
    }else if(!req.query.page && req.query.pageSize){
        sql += ` LIMIT ${num},${req.query.pageSize}`;
    }
    db.query(sql,(err,result)=>{
        if(err) return res.cc(err);
        res.s("请求数据成功",result,"200")
    })
})
router.get("/search_goods",(req,res)=>{
    if(!req.query.like)return res.s("like为必传值",[])
    if(!req.query.page){
        req.query.page = 0;
    }else if(!req.query.pageSize){
        req.query.pageSize = 15;
    }
    let sql = ` SELECT id,name,img_cover,money_now,xl FROM goods_all WHERE name LIKE '%${req.query.like}%' OR c_keyword LIKE '%${req.query.like}%' `;
    // 排序操作
    if(req.query.order && Object.keys(req.query.order).length > 0){
        var order = ` ORDER BY `;
        for(var i in req.query.order){
            order +=" "+ i + " DESC, ";
        }
        order = order.slice(0,order.lastIndexOf(","))
        sql += order;
    }
    sql += ` LIMIT ${req.query.page*req.query.pageSize},${req.query.pageSize} `;
    db.query(sql,(err,result)=>{
        if(err)return res.cc(err);
        res.s("搜索成功",result,"200")
    })
})
router.get("/province",(req,res)=>{
    let sql = `SELECT * FROM provinces`;
    db.query(sql,(err,result)=>{
        if(err)return res.cc(err)
        res.s("请求成功",result,"200")
    })
})
router.get("/city",(req,res)=>{
    let sql = `SELECT * FROM cities WHERE provinceid=${req.query.provinceid}`;
    db.query(sql,(err,result)=>{
        if(err)return res.cc(err);
        res.s("请求成功",result,"200");
    })
})
router.get("/areas",(req,res)=>{
    let sql = `SELECT * FROM areas`;
    db.query(sql,(err,result)=>{
        if(err)return res.cc(err)
        let newArr = [];
        result.forEach(item=>{
            if(req.query.qid == item.cityid){
                newArr.push(item);
            }
        })
        res.s("请求成功",newArr,"200")
    })
})
router.post("/get_addr",(req,res)=>{
    if(!req.body.uid) res.s("请传入用户id编号",[])
    let sql = `SELECT * FROM address WHERE user_id=? ORDER BY def DESC`;
    db.query(sql,[req.body.uid],(err,result)=>{
        if(err) return res.cc(err)
        res.s("获取地址数据成功",result,"200")
    })
})
router.post("/create_addr",(req,res)=>{
    if(!req.body.uid)return res.s("用户账号编号uid是必传参数")
    if(!req.body.takeover_addr)return res.s("用户地址takeover_addr是必传参数")
    if(!req.body.takeover_name)return res.s("收货人名称takeover_name是必传参数")
    if(!req.body.takeover_tel)return res.s("收货人手机号takeover_tel是必传参数")
    if(!req.body.def)req.body.def=0;
    if(!req.body.label)req.body.label="";
    let sql = `INSERT INTO address VALUES (null,?,?,?,?,?,?)`;
    let arr = [
            req.body.uid,
            req.body.takeover_name,
            req.body.takeover_tel,
            req.body.takeover_addr,
            req.body.label,
            req.body.def
        ]
    db.query(sql,arr,(err,result)=>{
        if(err)return res.cc(err)
        res.s("请求成功",result,"200")
    })
})
router.post("/update_addr",(req,res)=>{
    // fs读文件 渲染页面
    // if(!req.body.id)return res.s("id是必传参数")
    // console.log(req.body)
    // if(!req.body.uid)return res.s("用户账号编号uid是必传参数")
    // if(!req.body.takeover_addr)return res.s("用户地址takeover_addr是必传参数")
    // if(!req.body.takeover_name)return res.s("收货人名称takeover_name是必传参数")
    // if(!req.body.takeover_tel)return res.s("收货人手机号takeover_tel是必传参数")
    let keyArr = ["id","uid","takeover_name","takeover_tel","takeover_addr"];
    if(mustKey(req.body,keyArr,res)){
        console.log("程序停了");
        return;
    }
    if(!req.body.def)req.body.def=0;
    if(!req.body.takeover_label)req.body.takeover_label="";
    if(req.body.def){
        let sql = `UPDATE address SET def=0 uid=${req.body.uid}`;
        db.query(sql,(err,result)=>{
            if(err)return res.cc(err)
            fn();
        })
    }else{
        fn();
    }
    function fn(){
        let sql = `UPDATE address SET ${joinStr(req.body,["id","uid"])} WHERE id=?`;
        db.query(sql,[req.body.id],(err,result)=>{
            if(err)return res.cc(err)
            res.s("修改数据成功",result,"200")
        })
    }
    
})
router.post("/get_addrById",(req,res)=>{
    if(!req.body.id)return res.s("id为必传值,请传递参数id及值")
    let sql = `SELECT * FROM address WHERE id=?`;
    db.query(sql,[req.body.id],(err,result)=>{
        if(err)return res.cc(err)
        res.s("请求数据成功",result,"200")
    })
})
// 删除地址的接口(根据地址在表中的id 进行删除)
router.post("/del_addr",(req,res)=>{
    if(req.mustKey(req.body,["id"]))return;
    let sql = `DELETE FROM address WHERE id=?`;
    db.query(sql,[req.body.id],(err,result)=>{
        if(err)return res.cc(err)
        res.s("删除数据成功",result,"200")
    })
})
router.use((req,res,next)=>{
    req.mustKey = function(data,arr){
        for(var i=0;i<arr.length;i++){
            var temp = arr[i];
            if(!data[temp] == undefined){ //0 
                res.s("必须为接口传递"+temp+"属性和值",[])
                return true;
            }                                                                                                 
        }
        return false
    }
    next()
})
router.get("/hello",(req,res)=>{
    res.send("server running at 5122");
})
// data是通过req.query||req.body获取的
// arr是接口中必须存在的字段
function mustKey(data,arr,res){
    for(var i=0;i<arr.length;i++){
        var temp = arr[i];
        if(data[temp] == undefined){
            res.s("必须为接口传递"+arr[i]+"属性和值",[]);
            return true;
        }
    }
    return false;
}
function joinStr(obj,arr){
    var str = ``;
    for(var i in obj){
        if(arr.indexOf(i)== -1){
            str +=`${i}=\"${obj[i]}\",`
        }
    }
    str = str.slice(0,str.lastIndexOf(","));
    return str;
}
module.exports = router;